from datetime import datetime
from sqlalchemy import and_, exc, func
from app.model.RiskDictionaryModel import RiskDictionary
from app.config.db import DBUtils

class PortraitJyfxSffxTabService:
    def __init__(self):
        self.db_utils = DBUtils()
        # self.current_year = datetime.now().year
        # self.filter_pub = func.nvl(
        #     RiskDictionary.FBND,
        #     func.to_char(RiskDictionary.RQ, 'YYYY')
        # ) == str(self.current_year)

    #经营风险-公示催告
    def select_jyfx_gscg(self , session , company):
        try:
            # RiskDictionary.C1, #序号
            # RiskDictionary.C2, #票号
            # RiskDictionary.C3, #据类型
            # RiskDictionary.C4, #申请人
            # RiskDictionary.C5, #票面金额
            # RiskDictionary.C6, #公告日期 RiskDictionary.FXDL == '经营风险'
            jyfx_list = session.query(RiskDictionary).filter(
                                                and_(RiskDictionary.GSMC == company,
                                                  RiskDictionary.FXLX == '公示催告')).order_by(RiskDictionary.FBRQ.desc()).limit(30).all()
            if jyfx_list:
                result = [item.to_dict() for item in jyfx_list]
            else:
                result = []
            return result
        except exc.SQLAlchemyError as e:
            error_response = [{'error': str(e)}]
            try:
                self.db_utils.rollback()
            except Exception as rollback_error:
                print(f"Failed to rollback transaction: {rollback_error}")
            return error_response
        finally:
            try:
                self.db_utils.remove_session()
            except Exception as remove_error:
                print(f"Failed to rollback transaction: {remove_error}")

    #经营风险-股权出质
    def select_jyfx_gqcz(self , session , company):
        try:
            # RiskDictionary.C1,  # 股权出质设立登记日期
            # RiskDictionary.C2,  # 登记编号
            # RiskDictionary.C3,  # 出质人
            # RiskDictionary.C4,  # 质权人
            # RiskDictionary.C5,  # 处置股权数额
            # RiskDictionary.C6,  # 状态
            jyfx_list = session.query(RiskDictionary).filter(
                                                and_(RiskDictionary.GSMC == company,
                                                  RiskDictionary.FXLX == '股权出质')).order_by(RiskDictionary.FBRQ.desc()).limit(30).all()
            if jyfx_list:
                result = [item.to_dict() for item in jyfx_list]
            else:
                result = []
            return result
        except exc.SQLAlchemyError as e:
            error_response = [{'error': str(e)}]
            try:
                self.db_utils.rollback()
            except Exception as rollback_error:
                print(f"Failed to rollback transaction: {rollback_error}")
            return error_response
        finally:
            try:
                self.db_utils.remove_session()
            except Exception as remove_error:
                print(f"Failed to rollback transaction: {remove_error}")

    #经营风险-对外担保
    def select_jyfx_dwdb(self , session , company):
        try:
            # RiskDictionary.C1,  # 序号
            # RiskDictionary.C2,  # 公告日期
            # RiskDictionary.C3,  # 担保方
            # RiskDictionary.C4,  # 被担保方
            # RiskDictionary.C5,  # 担保方式
            # RiskDictionary.C6,  # 担保金额
            jyfx_list = session.query(RiskDictionary).filter(
                                                and_(RiskDictionary.GSMC == company,
                                                     RiskDictionary.FXLX == '对外担保')).order_by(RiskDictionary.FBRQ.desc()).limit(30).all()
            if jyfx_list:
                result = [item.to_dict() for item in jyfx_list]
            else:
                result = []
            return result
        except exc.SQLAlchemyError as e:
            error_response = [{'error': str(e)}]
            try:
                self.db_utils.rollback()
            except Exception as rollback_error:
                print(f"Failed to rollback transaction: {rollback_error}")
            return error_response
        finally:
            try:
                self.db_utils.remove_session()
            except Exception as remove_error:
                print(f"Failed to rollback transaction: {remove_error}")

    #经营风险-失信信息
    def select_jyfx_sxxx(self, session, company):
        try:
            # RiskDictionary.C1,  #
            # RiskDictionary.C2,  #
            # RiskDictionary.C3,  #
            # RiskDictionary.C4,  #
            # RiskDictionary.C5,  #
            # RiskDictionary.C6,  #
            jyfx_list = session.query(RiskDictionary).filter(
                                                and_(RiskDictionary.GSMC == company,
                                                     RiskDictionary.FXLX == '失信信息')).order_by(RiskDictionary.FBRQ.desc()).limit(30).all()
            if jyfx_list:
                result = [item.to_dict() for item in jyfx_list]
            else:
                result = []
            return result
        except exc.SQLAlchemyError as e:
            error_response = [{'error': str(e)}]
            try:
                self.db_utils.rollback()
            except Exception as rollback_error:
                print(f"Failed to rollback transaction: {rollback_error}")
            return error_response
        finally:
            try:
                self.db_utils.remove_session()
            except Exception as remove_error:
                print(f"Failed to rollback transaction: {remove_error}")

    #经营风险-股权质押
    def select_jyfx_gqzy(self, session, company):
        try:
            # RiskDictionary.C1,  #
            # RiskDictionary.C2,  #
            # RiskDictionary.C3,  #
            # RiskDictionary.C4,  #
            # RiskDictionary.C5,  #
            # RiskDictionary.C6,  #
            jyfx_list = session.query(RiskDictionary).filter(
                                                and_(RiskDictionary.GSMC == company,
                                                     RiskDictionary.FXLX == '股权质押')).order_by(RiskDictionary.FBRQ.desc()).limit(30).all()
            if jyfx_list:
                result = [item.to_dict() for item in jyfx_list]
            else:
                result = []
            return result
        except exc.SQLAlchemyError as e:
            error_response = [{'error': str(e)}]
            try:
                self.db_utils.rollback()
            except Exception as rollback_error:
                print(f"Failed to rollback transaction: {rollback_error}")
            return error_response
        finally:
            try:
                self.db_utils.remove_session()
            except Exception as remove_error:
                print(f"Failed to rollback transaction: {remove_error}")

    #经营风险-动产抵押
    def select_jyfx_dcdy(self , session , company):
        try:
            # RiskDictionary.C1,  # 登记日期
            # RiskDictionary.C2,  # 登记编号
            # RiskDictionary.C3,  # 登记机关
            # RiskDictionary.C4,  # 被担保债权类型
            # RiskDictionary.C5,  # 被担保债权数额
            # RiskDictionary.C6,  # 债务期限
            # RiskDictionary.C7,  # 担保范围
            # RiskDictionary.C8,  # 状态
            sffx_list = session.query(RiskDictionary).filter(
                                                and_(RiskDictionary.GSMC == company,
                                                  RiskDictionary.FXLX == '动产抵押')).order_by(RiskDictionary.FBRQ.desc()).limit(30).all()
            if sffx_list:
                result = [item.to_dict() for item in sffx_list]
            else:
                result = []
            return result
        except exc.SQLAlchemyError as e:
            error_response = [{'error': str(e)}]
            try:
                self.db_utils.rollback()
            except Exception as rollback_error:
                print(f"Failed to rollback transaction: {rollback_error}")
            return error_response
        finally:
            try:
                self.db_utils.remove_session()
            except Exception as remove_error:
                print(f"Failed to rollback transaction: {remove_error}")

    #经营风险-经营异常
    def select_jyfx_jyyc(self, session, company):
        try:
            # RiskDictionary.C1,  #
            # RiskDictionary.C2,  #
            # RiskDictionary.C3,  #
            # RiskDictionary.C4,  #
            # RiskDictionary.C5,  #
            # RiskDictionary.C6,  #
            jyfx_list = session.query(RiskDictionary).filter(
                                                and_(RiskDictionary.GSMC == company,
                                                     RiskDictionary.FXLX == '经营异常')).order_by(RiskDictionary.FBRQ.desc()).limit(30).all()
            if jyfx_list:
                result = [item.to_dict() for item in jyfx_list]
            else:
                result = []
            return result
        except exc.SQLAlchemyError as e:
            error_response = [{'error': str(e)}]
            try:
                self.db_utils.rollback()
            except Exception as rollback_error:
                print(f"Failed to rollback transaction: {rollback_error}")
            return error_response
        finally:
            try:
                self.db_utils.remove_session()
            except Exception as remove_error:
                print(f"Failed to rollback transaction: {remove_error}")

    #司法风险-司法协助
    def select_sffx_sfxz(self , session , company):
        try:
            # RiskDictionary.C1,  # 序号
            # RiskDictionary.C2,  # 被执行人
            # RiskDictionary.C3,  # 股权数额
            # RiskDictionary.C4,  # 执行通知文号
            # RiskDictionary.C5,  # 典型状态
            sffx_list = session.query(RiskDictionary).filter(
                                                and_(RiskDictionary.GSMC == company,
                                                  RiskDictionary.FXLX == '司法协助')).order_by(RiskDictionary.FBRQ.desc()).limit(30).all()
            if sffx_list:
                result = [item.to_dict() for item in sffx_list]
            else:
                result = []
            return result
        except exc.SQLAlchemyError as e:
            error_response = [{'error': str(e)}]
            try:
                self.db_utils.rollback()
            except Exception as rollback_error:
                print(f"Failed to rollback transaction: {rollback_error}")
            return error_response
        finally:
            try:
                self.db_utils.remove_session()
            except Exception as remove_error:
                print(f"Failed to rollback transaction: {remove_error}")

    #司法风险-开庭公告
    def select_sffx_ktgg(self , session , company):
        try:
            # RiskDictionary.C1,  # 序号
            # RiskDictionary.C2,  # 开庭日期
            # RiskDictionary.C3,  # 案由
            # RiskDictionary.C4,  # 案号
            # RiskDictionary.C5,  # 原告/上诉人
            # RiskDictionary.C6,  # 被告/被上诉人
            # RiskDictionary.C7,  # 法庭
            sffx_list = session.query(RiskDictionary).filter(
                                                and_(RiskDictionary.GSMC == company,
                                                  RiskDictionary.FXLX == '开庭公告')).order_by(RiskDictionary.FBRQ.desc()).limit(30).all()
            if sffx_list:
                result = [item.to_dict() for item in sffx_list]
            else:
                result = []
            return result
        except exc.SQLAlchemyError as e:
            error_response = [{'error': str(e)}]
            try:
                self.db_utils.rollback()
            except Exception as rollback_error:
                print(f"Failed to rollback transaction: {rollback_error}")
            return error_response
        finally:
            try:
                self.db_utils.remove_session()
            except Exception as remove_error:
                print(f"Failed to rollback transaction: {remove_error}")

    #司法风险-欠税公告
    def select_sffx_qsgg(self , session , company):
        try:
            # RiskDictionary.C1,  # 发布时间
            # RiskDictionary.C2,  # 纳税人识别号
            # RiskDictionary.C3,  # 欠税税种
            # RiskDictionary.C4,  # 欠税余额
            # RiskDictionary.C5,  # 负责人姓名
            # RiskDictionary.C6,  # 当前新发生欠税
            # RiskDictionary.C7,  # 税务机关
            sffx_list = session.query(RiskDictionary).filter(
                                                and_(RiskDictionary.GSMC == company,
                                                  RiskDictionary.FXLX == '欠税公告')).order_by(RiskDictionary.FBRQ.desc()).limit(30).all()
            if sffx_list:
                result = [item.to_dict() for item in sffx_list]
            else:
                result = []
            return result
        except exc.SQLAlchemyError as e:
            error_response = [{'error': str(e)}]
            try:
                self.db_utils.rollback()
            except Exception as rollback_error:
                print(f"Failed to rollback transaction: {rollback_error}")
            return error_response
        finally:
            try:
                self.db_utils.remove_session()
            except Exception as remove_error:
                print(f"Failed to rollback transaction: {remove_error}")

    #司法风险-法律诉讼
    def select_sffx_flss(self , session , company):
        try:
            # RiskDictionary.C1,  # 序号
            # RiskDictionary.C2,  # 日期
            # RiskDictionary.C3,  # 案号
            # RiskDictionary.C4,  # 类型号
            # RiskDictionary.C5,  # 案件名称
            sffx_list = session.query(RiskDictionary).filter(
                                                and_(RiskDictionary.GSMC == company,
                                                  RiskDictionary.FXLX == '法律诉讼')).order_by(RiskDictionary.FBRQ.desc()).limit(30).all()
            if sffx_list:
                result = [item.to_dict() for item in sffx_list]
            else:
                result = []
            return result
        except exc.SQLAlchemyError as e:
            error_response = [{'error': str(e)}]
            try:
                self.db_utils.rollback()
            except Exception as rollback_error:
                print(f"Failed to rollback transaction: {rollback_error}")
            return error_response
        finally:
            try:
                self.db_utils.remove_session()
            except Exception as remove_error:
                print(f"Failed to rollback transaction: {remove_error}")

    #司法风险-法院公告
    def select_sffx_fygg(self , session , company):
        try:
            # RiskDictionary.C1,  # 序号
            # RiskDictionary.C2,  # 公告日期
            # RiskDictionary.C3,  # 上诉方
            # RiskDictionary.C4,  # 被诉方
            # RiskDictionary.C5,  # 公告类型
            sffx_list = session.query(RiskDictionary).filter(
                                                and_(RiskDictionary.GSMC == company,
                                                  RiskDictionary.FXLX == '法院公告')).order_by(RiskDictionary.FBRQ.desc()).limit(30).all()
            if sffx_list:
                result = [item.to_dict() for item in sffx_list]
            else:
                result = []
            return result
        except exc.SQLAlchemyError as e:
            error_response = [{'error': str(e)}]
            try:
                self.db_utils.rollback()
            except Exception as rollback_error:
                print(f"Failed to rollback transaction: {rollback_error}")
            return error_response
        finally:
            try:
                self.db_utils.remove_session()
            except Exception as remove_error:
                print(f"Failed to rollback transaction: {remove_error}")

    #司法风险-环保处罚
    def select_sffx_hbcf(self , session , company):
        try:
            # RiskDictionary.C1,  # 序号
            # RiskDictionary.C2,  # 处罚日期
            # RiskDictionary.C3,  # 决定文书号
            # RiskDictionary.C4,  # 处罚事由
            # RiskDictionary.C5,  # 处罚结果
            # RiskDictionary.C6,  # 处罚单位
            sffx_list = session.query(RiskDictionary).filter(
                                                and_(RiskDictionary.GSMC == company,
                                                  RiskDictionary.FXLX == '环保处罚')).order_by(RiskDictionary.FBRQ.desc()).limit(30).all()
            if sffx_list:
                result = [item.to_dict() for item in sffx_list]
            else:
                result = []
            return result
        except exc.SQLAlchemyError as e:
            error_response = [{'error': str(e)}]
            try:
                self.db_utils.rollback()
            except Exception as rollback_error:
                print(f"Failed to rollback transaction: {rollback_error}")
            return error_response
        finally:
            try:
                self.db_utils.remove_session()
            except Exception as remove_error:
                print(f"Failed to rollback transaction: {remove_error}")

    #司法风险-立案信息
    def select_sffx_laxx(self , session , company):
        try:
            # RiskDictionary.C1,  # 序号
            # RiskDictionary.C2,  # 立案日期
            # RiskDictionary.C3,  # 案号
            # RiskDictionary.C4,  # 案件身份
            # RiskDictionary.C5,  # 法院
            sffx_list = session.query(RiskDictionary).filter(
                                              and_(RiskDictionary.GSMC == company,
                                                  RiskDictionary.FXLX == '立案信息')).order_by(RiskDictionary.FBRQ.desc()).limit(30).all()
            if sffx_list:
                result = [item.to_dict() for item in sffx_list]
            else:
                result = []
            return result
        except exc.SQLAlchemyError as e:
            error_response = [{'error': str(e)}]
            try:
                self.db_utils.rollback()
            except Exception as rollback_error:
                print(f"Failed to rollback transaction: {rollback_error}")
            return error_response
        finally:
            try:
                self.db_utils.remove_session()
            except Exception as remove_error:
                print(f"Failed to rollback transaction: {remove_error}")

    #司法风险-终本案件
    def select_sffx_zbaj(self , session , company):
        try:
            # RiskDictionary.C1,  # 序号
            # RiskDictionary.C2,  # 立案日期
            # RiskDictionary.C3,  # 案号
            # RiskDictionary.C4,  # 执行标的
            # RiskDictionary.C5,  # 未履行金额
            # RiskDictionary.C6,  # 执行法院
            # RiskDictionary.C7,  # 终本日期
            sffx_list = session.query(RiskDictionary).filter(
                                                and_(RiskDictionary.GSMC == company,
                                                  RiskDictionary.FXLX == '终本案件')).order_by(RiskDictionary.FBRQ.desc()).limit(30).all()
            if sffx_list:
                result = [item.to_dict() for item in sffx_list]
            else:
                result = []
            return result
        except exc.SQLAlchemyError as e:
            error_response = [{'error': str(e)}]
            try:
                self.db_utils.rollback()
            except Exception as rollback_error:
                print(f"Failed to rollback transaction: {rollback_error}")
            return error_response
        finally:
            try:
                self.db_utils.remove_session()
            except Exception as remove_error:
                print(f"Failed to rollback transaction: {remove_error}")

    #司法风险-行政处罚
    def select_sffx_xzcf(self , session , company):
        try:
            # RiskDictionary.C1,  # 序号
            # RiskDictionary.C2,  # 处罚日期
            # RiskDictionary.C3,  # 决定文书号
            # RiskDictionary.C4,  # 处罚事由/违法行为类型
            # RiskDictionary.C5,  # 处罚结果/内容
            # RiskDictionary.C6,  # 处罚单位
            sffx_list = session.query(RiskDictionary).filter(
                                                and_(RiskDictionary.GSMC == company,
                                                  RiskDictionary.FXLX == '行政处罚')).order_by(RiskDictionary.FBRQ.desc()).limit(30).all()
            if sffx_list:
                result = [item.to_dict() for item in sffx_list]
            else:
                result = []
            return result
        except exc.SQLAlchemyError as e:
            error_response = [{'error': str(e)}]
            try:
                self.db_utils.rollback()
            except Exception as rollback_error:
                print(f"Failed to rollback transaction: {rollback_error}")
            return error_response
        finally:
            try:
                self.db_utils.remove_session()
            except Exception as remove_error:
                print(f"Failed to rollback transaction: {remove_error}")

    #司法风险-被执行人信息
    def select_sffx_bzxrxx(self , session , company):
        try:
            # RiskDictionary.C1,  # 序号
            # RiskDictionary.C2,  # 立案时间期
            # RiskDictionary.C3,  # 执行标的
            # RiskDictionary.C4,  # 案号份
            # RiskDictionary.C5,  # 执行法院
            sffx_list = session.query(RiskDictionary).filter(
                                                and_(RiskDictionary.GSMC == company,
                                                  RiskDictionary.FXLX == '被执行人信息')).order_by(RiskDictionary.FBRQ.desc()).limit(30).all()
            if sffx_list:
                result = [item.to_dict() for item in sffx_list]
            else:
                result = []
            return result
        except exc.SQLAlchemyError as e:
            error_response = [{'error': str(e)}]
            try:
                self.db_utils.rollback()
            except Exception as rollback_error:
                print(f"Failed to rollback transaction: {rollback_error}")
            return error_response
        finally:
            try:
                self.db_utils.remove_session()
            except Exception as remove_error:
                print(f"Failed to rollback transaction: {remove_error}")

    #司法风险-送达公告
    def select_sffx_sdgg(self , session , company):
        try:
            # RiskDictionary.C1,  # 序号
            # RiskDictionary.C2,  # 发布日期
            # RiskDictionary.C3,  # 公告名称
            # RiskDictionary.C4,  # 案号
            # RiskDictionary.C5,  # 案由
            # RiskDictionary.C6,  # 案件身份
            sffx_list = session.query(RiskDictionary).filter(
                                                and_(RiskDictionary.GSMC == company,
                                                  RiskDictionary.FXLX == '送达公告')).order_by(RiskDictionary.FBRQ.desc()).limit(30).all()
            if sffx_list:
                result = [item.to_dict() for item in sffx_list]
            else:
                result = []
            return result
        except exc.SQLAlchemyError as e:
            error_response = [{'error': str(e)}]
            try:
                self.db_utils.rollback()
            except Exception as rollback_error:
                print(f"Failed to rollback transaction: {rollback_error}")
            return error_response
        finally:
            try:
                self.db_utils.remove_session()
            except Exception as remove_error:
                print(f"Failed to rollback transaction: {remove_error}")

    #司法风险-限制消费令
    def select_sffx_xzxfl(self , session , company):
        try:
            # RiskDictionary.C1,  # 序号
            # RiskDictionary.C2,  # 立案日期
            # RiskDictionary.C3,  # 案号
            # RiskDictionary.C4,  # 姓名
            # RiskDictionary.C5,  # 企业信息
            # RiskDictionary.C6,  # 申请人
            # RiskDictionary.C7,  # 发布时间
            sffx_list = session.query(RiskDictionary).filter(
                                                and_(RiskDictionary.GSMC == company,
                                                  RiskDictionary.FXLX == '限制消费令')).order_by(RiskDictionary.FBRQ.desc()).limit(30).all()
            if sffx_list:
                result = [item.to_dict() for item in sffx_list]
            else:
                result = []
            return result
        except exc.SQLAlchemyError as e:
            error_response = [{'error': str(e)}]
            try:
                self.db_utils.rollback()
            except Exception as rollback_error:
                print(f"Failed to rollback transaction: {rollback_error}")
            return error_response
        finally:
            try:
                self.db_utils.remove_session()
            except Exception as remove_error:
                print(f"Failed to rollback transaction: {remove_error}")
